BEGIN;

-- -----------------------------------------------------
-- pays
-- -----------------------------------------------------
CREATE TABLE pays (
  pays_id serial PRIMARY KEY,
  code_pays CHARACTER VARYING(2) NOT NULL UNIQUE,
  pays CHARACTER VARYING(45) NOT NULL UNIQUE
);

-- -----------------------------------------------------
-- ville
-- -----------------------------------------------------
CREATE TABLE ville (
  ville_id serial PRIMARY KEY,
  code_postal CHARACTER VARYING(5) NOT NULL UNIQUE,
  ville CHARACTER VARYING(50) NOT NULL,
  pays_fk INT NOT NULL,
  CONSTRAINT ville_pays_fk
      FOREIGN KEY (pays_fk)
      REFERENCES pays(pays_id)
      ON UPDATE CASCADE ON DELETE CASCADE
);


-- -----------------------------------------------------
-- etablissement
-- -----------------------------------------------------
CREATE TABLE etablissement (
  etablissement_id serial PRIMARY KEY,
  nom CHARACTER VARYING(45) NOT NULL,
  rue CHARACTER VARYING(45) NOT NULL,
  ville_fk INT NOT NULL,
  telephone CHARACTER VARYING(10),
  CONSTRAINT etablissement_ville_fk
      FOREIGN KEY (ville_fk)
      REFERENCES ville(ville_id)
      ON UPDATE CASCADE ON DELETE CASCADE
);


-- -----------------------------------------------------
-- medecin
-- -----------------------------------------------------
CREATE TABLE medecin (
  medecin_id serial PRIMARY KEY,
  nom CHARACTER VARYING(45) NOT NULL,
  prenom CHARACTER VARYING(45) NOT NULL,
  telephone CHARACTER VARYING(10),
  rue CHARACTER VARYING(45) NOT NULL,
  ville_fk INT NOT NULL,
  email CHARACTER VARYING(45),
  CONSTRAINT medecin_ville_fk
      FOREIGN KEY (ville_fk)
      REFERENCES ville(ville_id)
      ON UPDATE CASCADE ON DELETE CASCADE
);


-- -----------------------------------------------------
-- eleve
-- -----------------------------------------------------
CREATE TABLE eleve (
  eleve_id serial NOT NULL,
  matricule character varying(15) NOT NULL,
  nom character varying(45) NOT NULL,
  prenom character varying(45) NOT NULL,
  date_naissance date,
  ville_naissance_fk integer NOT NULL,
  nationalite_fk integer NOT NULL,
  -- 'h' ou 'f'
  sexe character varying(1) NOT NULL,
  rue character varying(45) NOT NULL,
  ville_fk integer NOT NULL,
  email character varying(45),
  photo character varying(45),
  date_inscription timestamp without time zone,
  etablissement_precedent_fk integer,
  tel_domicile character varying(10),
  tel_mobile character varying(10),
  tel_mobile_2 character varying(10),
  medecin_fk integer,
  remarques_medicales text,
  vaccinations text,
  allergies text,
  CONSTRAINT eleve_pkey PRIMARY KEY (eleve_id ),
  CONSTRAINT eleve_etablissement_precedent_fk FOREIGN KEY (etablissement_precedent_fk)
      REFERENCES etablissement (etablissement_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT eleve_medecin_fk FOREIGN KEY (medecin_fk)
      REFERENCES medecin (medecin_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT eleve_nationalite_fk FOREIGN KEY (nationalite_fk)
      REFERENCES pays (pays_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT eleve_ville_fk FOREIGN KEY (ville_fk)
      REFERENCES ville (ville_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT eleve_ville_naissance_fk FOREIGN KEY (ville_naissance_fk)
      REFERENCES ville (ville_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT eleve_matricule_key UNIQUE (matricule )
);


-- -----------------------------------------------------
-- responsable
-- -----------------------------------------------------
CREATE TABLE responsable (
  responsable_id serial NOT NULL,
  nom character varying(45) NOT NULL,
  prenom character varying(45) NOT NULL,
  rue character varying(45) NOT NULL,
  ville_fk integer NOT NULL,
  tel_fix character varying(10),
  tel_mobile character varying(10),
  tel_pro character varying(10),
  email character varying(45),
  CONSTRAINT responsable_pkey PRIMARY KEY (responsable_id ),
  CONSTRAINT responsable_ville_fk FOREIGN KEY (ville_fk)
      REFERENCES ville (ville_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);


-- -----------------------------------------------------
-- eleve_responsable_link
-- -----------------------------------------------------
CREATE TABLE eleve_responsable_link (
  eleve_responsable_id serial NOT NULL,
  eleve_fk integer NOT NULL,
  responsable_fk integer NOT NULL,
  CONSTRAINT eleve_responsable_link_pkey PRIMARY KEY (eleve_responsable_id ),
  CONSTRAINT eleve_responsable_link_eleve_fk FOREIGN KEY (eleve_fk)
      REFERENCES eleve (eleve_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT eleve_responsable_link_responsable_fk FOREIGN KEY (responsable_fk)
      REFERENCES responsable (responsable_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT eleve_responsable_link_unique UNIQUE (eleve_fk , responsable_fk )
);


-- -----------------------------------------------------
-- matiere
-- -----------------------------------------------------
CREATE TABLE matiere (
  matiere_id serial NOT NULL,
  nom character varying(45) NOT NULL,
  CONSTRAINT matiere_pkey PRIMARY KEY (matiere_id ),
  CONSTRAINT matiere_nom_key UNIQUE (nom )
);

-- -----------------------------------------------------
-- niveau
-- -----------------------------------------------------
CREATE TABLE niveau (
  niveau_id serial NOT NULL,
  nom character varying(45) NOT NULL,
  CONSTRAINT niveau_pkey PRIMARY KEY (niveau_id ),
  CONSTRAINT niveau_nom_key UNIQUE (nom )
);

-- -----------------------------------------------------
-- classe
-- -----------------------------------------------------
CREATE TABLE classe (
  classe_id serial NOT NULL,
  nom character varying(45) NOT NULL,
  annee integer NOT NULL,
  niveau_fk integer NOT NULL,
  CONSTRAINT classe_pkey PRIMARY KEY (classe_id ),
  CONSTRAINT classe_niveau_fk FOREIGN KEY (niveau_fk)
      REFERENCES niveau (niveau_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT classe_unique UNIQUE (nom , annee )
);


-- -----------------------------------------------------
-- eleve_classe_link
-- -----------------------------------------------------
CREATE TABLE eleve_classe_link (
  eleve_classe_link_id serial NOT NULL,
  eleve_fk integer NOT NULL,
  classe_fk integer NOT NULL,
  CONSTRAINT eleve_classe_link_pkey PRIMARY KEY (eleve_classe_link_id ),
  CONSTRAINT eleve_classe_link_classe_fk FOREIGN KEY (classe_fk)
      REFERENCES classe (classe_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT eleve_classe_link_eleve_fk FOREIGN KEY (eleve_fk)
      REFERENCES eleve (eleve_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT eleve_classe_link_unique UNIQUE (eleve_fk , classe_fk )
);


-- -----------------------------------------------------
-- enseignant
-- -----------------------------------------------------
CREATE TABLE enseignant (
  enseignant_id serial NOT NULL,
  nom character varying(45) NOT NULL,
  prenom character varying(45) NOT NULL,
  rue character varying(45) NOT NULL,
  ville_fk integer NOT NULL,
  CONSTRAINT enseignant_pkey PRIMARY KEY (enseignant_id ),
  CONSTRAINT enseignant_ville_fk FOREIGN KEY (ville_fk)
      REFERENCES ville (ville_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);


-- -----------------------------------------------------
-- classe_matiere_link
-- -----------------------------------------------------
CREATE TABLE classe_matiere_link (
  classe_matiere_link_id serial NOT NULL,
  classe_fk integer NOT NULL,
  matiere_fk integer NOT NULL,
  enseignant_fk integer,
  CONSTRAINT classe_matiere_link_pkey PRIMARY KEY (classe_matiere_link_id ),
  CONSTRAINT classe_matiere_link_classe_fk FOREIGN KEY (classe_fk)
      REFERENCES classe (classe_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT classe_matiere_link_enseignant_fk FOREIGN KEY (enseignant_fk)
      REFERENCES enseignant (enseignant_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT classe_matiere_link_matiere_fk FOREIGN KEY (matiere_fk)
      REFERENCES matiere (matiere_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT classe_matiere_link_unique UNIQUE (classe_fk , matiere_fk )
);


-- -----------------------------------------------------
-- note
-- -----------------------------------------------------
CREATE TABLE note (
  note_id serial NOT NULL,
  eleve_fk integer NOT NULL,
  classe_fk integer NOT NULL,
  matiere_fk integer NOT NULL,
  periode integer NOT NULL,
  num_controle integer,
  note double precision,
  CONSTRAINT note_pkey PRIMARY KEY (note_id ),
  CONSTRAINT note_classe_fk FOREIGN KEY (classe_fk)
      REFERENCES classe (classe_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT note_eleve_fk FOREIGN KEY (eleve_fk)
      REFERENCES eleve (eleve_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT note_matiere_fk FOREIGN KEY (matiere_fk)
      REFERENCES matiere (matiere_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT note_unique UNIQUE (eleve_fk , classe_fk , matiere_fk , periode , num_controle )
);

COMMIT;

